/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.blog.idao.IAuthorDAO;
import org.blog.model.Author;

/**
 *
 * @author danang
 */
public class AuthorDAO implements IAuthorDAO {

    private Connection connection;

    public AuthorDAO(Connection connection) {
        this.connection = connection;
    }

    public int insert(Author author) throws SQLException {
        String sql = "insert into author(username,password,real_name,about) values(?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, author.getUsername());
        ps.setString(2, author.getPassword());
        ps.setString(3, author.getRealName());
        ps.setString(4, author.getAbout());

        return ps.executeUpdate();
    }

    public int update(int id, Author authorUpdate) throws SQLException {
        String sql = "update author set username=?, password=?, real_name=?, about=? "
                + "where author_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, authorUpdate.getUsername());
        ps.setString(2, authorUpdate.getPassword());
        ps.setString(3, authorUpdate.getRealName());
        ps.setString(4, authorUpdate.getAbout());
        ps.setInt(5, id);

        return ps.executeUpdate();
    }

    public int delete(int id) throws SQLException {
        String sql = "delete from author where author_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setInt(1, id);

        return ps.executeUpdate();
    }

    public Author getByUsername(String username) throws SQLException {
        String sql = "select author_id,password,real_name,about from author " +
                "where username=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();
        Author author = null;

        while(rs.next()){
            author = new Author();
            author.setId(rs.getInt("author_id"));
            author.setUsername(username);
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));
        }

        return author;
    }

    public Author getById(int id) throws SQLException {
        String sql = "select username, password, real_name, about from author" +
                " where author_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Author author = null;

        while(rs.next()){
            author = new Author();
            author.setId(id);
            author.setUsername(rs.getString("username"));
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));
        }

        return author;
    }

    public List<Author> getAll() throws SQLException {
        String sql = "select author_id,username,password,real_name,about from author";
        Statement st = this.connection.createStatement();
        ResultSet rs = st.executeQuery(sql);
        List<Author> listAuthor = new ArrayList<Author>();
        while(rs.next()){
            Author author = new Author();
            author.setId(rs.getInt("author_id"));
            author.setUsername(rs.getString("username"));
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));
            listAuthor.add(author);
        }
        return listAuthor;
    }

    public List<Author> getByRealNameLike(String name) throws SQLException {
        String sql = "select * from author where real_name like ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, "%" + name + "%");
        ResultSet rs = ps.executeQuery();
        List<Author> listAuthor = new ArrayList<Author>();

        while(rs.next()){
            Author author = new Author();
            author.setId(rs.getInt("author_id"));
            author.setUsername(rs.getString("username"));
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));
            listAuthor.add(author);
        }
        return listAuthor;
    }
}
